-- @owner: Alice
-- @date: 2025-09-03
-- @testpoint: 确定性函数支持result_cache,各种内置过程的语言，合理报错
@conn PrimaryDbAmode;
drop extension if exists gms_output;
drop table if exists tab_int4a;
drop type if exists int4a cascade;
drop function if exists func_add_sql;
drop function if exists func_increment_sql;
drop function if exists func_dup_sql;
drop function if exists func_add_sql2;
drop function if exists int4a_in;
drop function if exists int4a_out;
create extension gms_output;
--case1 内置过程语言为sql
set enable_func_cache = 1;
select gms_output.enable();
set query_dop=1;


set behavior_compat_options='skip_insert_gs_source,select_into_return_null';
--set behavior_compat_options='skip_insert_gs_source,select_into_return_null,proc_outparam_override';
--创建表
drop table if exists tt_cache;
create table tt_cache(
id int,
col_smallint smallint
);
insert into tt_cache values(
generate_series(1,20)%4,
generate_series(1,20)%3
);
select count(*) from tt_cache;
drop function if exists func_add_sql;
create or replace function func_add_sql(integer, integer) RETURNS integer
AS 'select $1 + $2;'
LANGUAGE SQL
/
IMMUTABLE
RESULT_CACHE
RETURNS NULL ON NULL INPUT;
SELECT p.proname,pe.result_cache FROM pg_proc p JOIN pg_proc_ext pe ON p.oid = pe.proc_oid WHERE p.proname = 'func_add_sql' order by p.proname;
SELECT func_add_sql(1,1);
SELECT func_add_sql(id,col_smallint) from tt_cache;
SELECT func_add_sql(id,func_add_sql(id,col_smallint)) from tt_cache;
select * from func_add_sql(1,1);
--case2
create or replace function func_increment_sql(i int, out result_1 bigint, out result_2 bigint)
returns SETOF RECORD
as $$
begin
raise notice 'NOTICE: excute function...';
result_1 = i + 1;
result_2 = i * 10;
return next;
end;
$$language plpgsql;
/
SELECT p.proname,pe.result_cache FROM pg_proc p JOIN pg_proc_ext pe ON p.oid = pe.proc_oid WHERE p.proname = 'func_increment_sql' order by p.proname;
SELECT func_increment_sql(1);
SELECT func_increment_sql(1) from tt_cache;
select * from func_increment_sql(1);
--case3
create or replace function func_dup_sql(in int, out f1 int, out f2 text) immutable result_cache
AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
LANGUAGE SQL;
/
SELECT * FROM func_dup_sql(42);
SELECT func_increment_sql(col_smallint) from tt_cache;
select * from func_increment_sql(1);
--case5
create or replace function func_add_sql2(num1 integer, num2 integer) RETURN integer result_cache stable
AS
BEGIN
raise notice 'NOTICE: excute function...';
RETURN num1 + num2;
END;
/
select func_add_sql2(12,19);
SELECT func_add_sql2(func_add_sql2(id,col_smallint),col_smallint) from tt_cache;
select func_add_sql2(12,func_add_sql2(12,19));
select func_add_sql2(func_add_sql2(12,19),func_add_sql2(12,19));
select func_add_sql2(func_add_sql2(12,19),func_add_sql2(12,19)),func_add_sql2(func_add_sql2(12,19),func_add_sql2(12,19));
select func_add_sql2(func_add_sql2(12,19),func_add_sql2(14,19)),func_add_sql2(func_add_sql2(13,19),func_add_sql2(12,19));
SELECT func_add_sql2(id,col_smallint::int) from tt_cache;
SELECT func_add_sql2(id,col_smallint::int) from tt_cache order by id,col_smallint;
SELECT func_add_sql2(id,col_smallint::int) from tt_cache order by col_smallint,id;
SELECT func_add_sql2(func_add_sql2(id,col_smallint),col_smallint) from tt_cache;
select * from func_increment_sql(1);
--internal
drop table if exists tab_int4a;
drop type if exists int4a cascade;
create type int4a;
create or replace function int4a_in(cstring) returns int4a as 'int4in' result_cache language internal strict;
/
create or replace function int4a_out(int4a) returns cstring as 'int4out' result_cache language internal strict;
/
SELECT p.proname,p.prosrc,pe.result_cache FROM pg_proc p JOIN pg_proc_ext pe ON p.oid = pe.proc_oid where p.proname in ('int4a_in','int4a_out') order by p.proname;
create type int4a (internallength=4,input=int4a_in,output=int4a_out,alignment=int4,default=1000,passedbyvalue);
create table tab_int4a (f1 int4a);
insert into tab_int4a default values;
select * from tab_int4a;
--teardown
drop table if exists tab_int4a;
drop type if exists int4a cascade;
drop function if exists func_add_sql;
drop function if exists func_increment_sql;
drop function if exists func_dup_sql;
drop function if exists func_add_sql2;
drop function if exists int4a_in;
drop function if exists int4a_out;
